﻿using System;
using System.Data;
using System.IO;
using NPOI.HSSF.UserModel;

namespace Utility
{
    public class ExcelBarcode
    {
        public static ExcelBarcode Instance = new ExcelBarcode();

        private ExcelBarcode() { }

        private HSSFWorkbook workbook;

        /// <summary>
        /// 初始化工作簿
        /// </summary>
        /// <param name="path"></param>
        private HSSFWorkbook InitWorkbook()
        {
            workbook = new HSSFWorkbook();

            return workbook;
        }

        private HSSFSheet InitSheet(HSSFWorkbook workbook, string sheetName)
        {
            return workbook.CreateSheet(sheetName);
        }

        private HSSFCell InitCell(HSSFRow row, int col, HSSFCellStyle style)
        {
            HSSFCell cell = row.CreateCell(col);

            if (style != null)
                cell.CellStyle = style;

            return cell;
        }

        public bool ExportToExcel(DataSet ds, DataTable dtSheetName, string path)
        {
            try
            {
                HSSFWorkbook workbook = InitWorkbook();

                if (ds.Tables.Count != dtSheetName.Rows.Count)
                    return false;

                for (int k = 0; k < ds.Tables.Count; ++k)
                {
                    DataTable dt = ds.Tables[k];

                    HSSFSheet sheet = InitSheet(workbook, dtSheetName.Rows[k]["SHEETNAME"].ToString());

                    DataTableInsert(dt, workbook, sheet);
                }

                SaveExcel(path);

                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        public bool ExportToExcel(DataTable dt, string sheetName, string path)
        {
            try
            {
                HSSFWorkbook workbook = InitWorkbook();

                HSSFSheet sheet = InitSheet(workbook, sheetName);

                DataTableInsert(dt, workbook, sheet);

                SaveExcel(path);

                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        private void DataTableInsert(DataTable dt, HSSFWorkbook workbook, HSSFSheet sheet)
        {
            for (int i = 0; i < dt.Rows.Count; ++i)
            {
                int col = 0;
                HSSFRow row = sheet.CreateRow(i);

                foreach (DataColumn dc in dt.Columns)
                {
                    HSSFFont font = null;

                    string colName = dc.ColumnName;
                    if (colName.ToLower().IndexOf("font") >= 0 || colName.ToLower().IndexOf("point") >= 0)
                        continue;

                    if (dt.Columns.Contains(colName + "_FONT"))
                    {
                        font = workbook.CreateFont();
                        font.FontName = dt.Rows[i][colName + "_FONT"].ToString();
                    }
                    if (dt.Columns.Contains(colName + "_POINT"))
                    {
                        if (font == null)
                            font = workbook.CreateFont();

                        font.FontHeightInPoints = short.Parse(dt.Rows[i][colName + "_POINT"].ToString());
                    }

                    HSSFCellStyle style = workbook.CreateCellStyle();
                    if (font != null)
                        style.SetFont(font);

                    HSSFCell cell = InitCell(row, col, style);

                    cell.SetCellValue(dt.Rows[i][colName].ToString());

                    sheet.SetColumnWidth(col++, dt.Rows[i][colName].ToString().Length * 256 * 3);
                }
            }
        }

        private void SaveExcel(string filePath)
        {
            FileStream file = null;

            filePath = filePath.Replace("\\", "/");
            string path = filePath.Substring(0, filePath.LastIndexOf("/") + 1);

            if (!Directory.Exists(path))
                Directory.CreateDirectory(path);

            file = new FileStream(filePath, FileMode.Create, FileAccess.ReadWrite);
            workbook.Write(file);
            file.Close();
        }
    }
}